package org.execute.tool.excel处理.对象转为excel.查询语句的结果转为excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class ExcelUtil {

    public static  void exportToExcel(List<ResulTable> resulTables, String path) throws IOException {
        Date now = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("HH_mm");
        SimpleDateFormat nyr = new SimpleDateFormat("yyyy_MM_dd");
        String timeString = sdf.format(now);
        String nyrString = nyr.format(now);
        String filePath = path + "\\" + nyrString + ".xlsx";
        File file = new File(filePath);
        boolean exists = file.exists();



        if (resulTables.isEmpty()) {
            throw new IllegalArgumentException("Data list cannot be empty for export.");
        }
        Workbook workbook;

        if (exists) {
            try (FileInputStream fis = new FileInputStream(file)) {
                workbook = new XSSFWorkbook(fis);
            }
        } else {
            workbook = new XSSFWorkbook();
        }

        Sheet sheet = workbook.createSheet(timeString);

        int rowNum = sheet.getLastRowNum();
        if (rowNum==-1) {
            rowNum = 0;
        }
        for (int i = 0; i < resulTables.size(); i++) {
            int i1 = exportToSheet(sheet, resulTables.get(i), rowNum);
            rowNum=i1;
        }

        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        }
        workbook.close();
    }

    static int    exportToSheet(Sheet sheet, ResulTable resulTable, Integer rowNum){

        //介绍
        ExecuteSql executeSql = resulTable.getExecuteSql();
        Row nameRow = sheet.createRow(rowNum);
        Cell cellName = nameRow.createCell(0);
        cellName.setCellValue(executeSql.getName());
        rowNum=rowNum+1;
        Row descriptionRow = sheet.createRow(rowNum);
        Cell cellDescription = descriptionRow.createCell(0);
        cellDescription.setCellValue(executeSql.getDescription());
        rowNum=rowNum+1;
        Row creationTimeRow = sheet.createRow(rowNum);
        Cell cellCreationTime = creationTimeRow.createCell(0);
        cellCreationTime.setCellValue(resulTable.getCreationTime());
        rowNum=rowNum+1;

        //头部
        Row headerRow = sheet.createRow(rowNum);
        for (int i = 0; i < resulTable.getHeader().size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(resulTable.getHeader().get(i));
        }
        rowNum=rowNum+1;
        //描述

        for (int i = 0; i < resulTable.getRow().size(); i++) {
            Row tableRow = sheet.createRow(rowNum);
            for (int j = 0; j < resulTable.getRow().get(i).size(); j++) {
                Cell cell = tableRow.createCell(j);
                if (resulTable.getRow().get(i).get(j)!=null){
                    Object value = resulTable.getRow().get(i).get(j);
                    if (value instanceof String) {
                        cell.setCellValue((String) value);
                    } else if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof Double) {
                        cell.setCellValue((Double) value);
                    } else if (value instanceof Boolean) {
                        cell.setCellValue((Boolean) value);
                    } else if (value instanceof Date) {
                        cell.setCellValue(((Date) value).toString());
                    } else {
                        cell.setCellValue(value.toString());
                    }
                }
            }
            rowNum=rowNum+1;

        }
        return rowNum;
    }


    }
